from flask import (
    Flask,
    render_template,
    request,
    redirect,
    url_for,
    send_file,
    Response,
)
from flask.helpers import abort
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from faker import Faker
import openpyxl
from io import BytesIO
import os
import time
import shutil

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///todos.db"
db = SQLAlchemy(app)


class Todo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    task = db.Column(db.String, nullable=False)
    completed = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(
        db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow
    )
    # 添加新的大文本任务描述字段
    description = db.Column(db.Text)
    privilege = db.Column(db.String, default="Mid")
    weekly = db.Column(db.String, default=lambda: datetime.utcnow().strftime("%A"))


@app.route("/add_task_page")
def add_task_page():
    return render_template("add_task.html")


@app.route("/fake_data")
def fake_data():
    faker = Faker()
    for _ in range(10):
        new_todo = Todo(
            task=faker.sentence(),
            completed=faker.boolean(),
            created_at=datetime.now(),
            updated_at=datetime.now(),
            description=faker.paragraph(),
            weekly=datetime.utcnow().strftime("%A"),
        )
        db.session.add(new_todo)
    db.session.commit()
    return redirect(url_for("index"))


@app.route("/")
def index():
    page = request.args.get("page", 1, type=int)
    per_page = 6
    todos = Todo.query.paginate(page=page, per_page=per_page)
    total_pages = todos.pages
    limit = 30
    for todo in todos.items:  # 使用.items 获取当前页的实际数据
        task = todo.task
        if len(task) > limit:
            todo.task = task[:limit] + " ..."
        todo.created_at = todo.created_at.strftime("%Y-%m-%d %H:%M")
        todo.updated_at = todo.updated_at.strftime("%Y-%m-%d %H:%M")

    return render_template("index.html", todos=todos, total_pages=total_pages)


@app.route("/add", methods=["POST"])
def add():
    task = request.form["task"]
    description = request.form["description"]
    privilege = request.form["privilege"]
    new_todo = Todo(task=task, description=description, privilege=privilege)
    db.session.add(new_todo)
    db.session.commit()
    return redirect(url_for("index"))


@app.route("/toggle/<int:todo_id>")
def toggle(todo_id):
    page = request.args.get("page", 1, type=int)
    todo = Todo.query.get(todo_id)
    todo.completed = not todo.completed
    db.session.commit()
    return redirect(url_for("index", page=page))


@app.route("/delete/<int:todo_id>")
def delete(todo_id):
    todo = Todo.query.get(todo_id)
    page = request.args.get("page", 1, type=int)
    db.session.delete(todo)
    db.session.commit()
    return redirect(url_for("index", page=page))


@app.route("/todo/<int:todo_id>")
def todo_details(todo_id):
    todo = Todo.query.get(todo_id)
    if todo:
        return render_template("todo_details.html", todo=todo)
    return "Todo not found", 404


@app.route("/edit_todo/<int:todo_id>", methods=["POST"])
def edit_todo(todo_id):
    new_task = request.form["new_task"]
    new_description = request.form["new_description"]
    privilege = request.form["privilege"]
    completed = request.form["completed"]
    todo = Todo.query.get(todo_id)
    todo.task = new_task
    todo.description = new_description
    todo.privilege = privilege
    todo.completed = completed == "True"
    db.session.commit()
    return redirect(url_for("todo_details", todo_id=todo_id))


@app.route("/export")
def export_data():
    # 创建一个新的工作簿
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Todos"

    # 添加表头
    ws.append(
        [
            "ID",
            "Task",
            "Weekly",
            "Privilege",
            "Completed",
            "Created At",
            "Updated At",
            "Description",
        ]
    )

    # 获取所有的 Todo 数据并添加到工作表中
    todos = Todo.query.all()
    for todo in todos:
        ws.append(
            [
                todo.id,
                todo.task,
                todo.weekly,
                todo.privilege,
                todo.completed,
                todo.created_at,
                todo.updated_at,
                todo.description,
            ]
        )

    # 将工作簿保存到内存中
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    now = datetime.now()
    filename = f'todos_{now.strftime("%Y-%m-%d_%H-%M-%S")}.xlsx'

    # 返回.xlsx 文件作为响应
    return send_file(output, as_attachment=True, download_name=filename)


@app.route("/backup")
def backup_database():
    backup_filename = f"backup_{int(time.time())}.db"
    backup_path = os.path.join(os.getcwd(), backup_filename)
    result = ""
    try:
        # 获取当前数据库文件路径
        current_db_filename = app.config["SQLALCHEMY_DATABASE_URI"].split("///")[1]
        current_db_path = os.path.join(os.getcwd(), "instance", current_db_filename)
        print(f"{current_db_path}, {backup_path}")
        # 复制数据库文件进行备份
        shutil.copy2(current_db_path, backup_path)
        result = f"backup success: \n{backup_filename}"
    except Exception as e:
        result = f"Backup failed: \n{str(e)}"
    return render_template("backup.html", result=result)


@app.errorhandler(404)
def page_not_found(e):
    return render_template("404.html"), 404


if __name__ == "__main__":
    with app.app_context():
        db.create_all()
    app.run(debug=True)
